package demo.database.sql2o.Sql2o;

import java.util.List;

import org.sql2o.Connection;
import org.sql2o.Query;
import org.sql2o.StatementRunnable;

import com.titan.core.utils.db.DBUtils;

import demo.database.hsqldb.MemoryOnlyDBManager;



/**
 *
 *
 * @author Administrator
 *
 */
public class Transactions {
    public static void main(String[] args) {
        try {
            java.sql.Connection conn = null;
            try {
                // 初始化数据表
                conn = MemoryOnlyDBManager.getConnection();
                DBUtils.execute(conn, "CREATE TABLE SomeTable(id DECIMAL, description VARCHAR(100))");
            } finally {
                DBUtils.close(conn);
            }            
            
            tran1();
            tran2();
            tran3();
            
            String sql = "SELECT id, description FROM SomeTable";
            List<TaskDomain> items = Configuration.getSql2o().createQuery(sql).executeAndFetch(TaskDomain.class);
            for (TaskDomain task : items) {
                System.out.println(task);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } catch (Throwable t) {
            t.printStackTrace();
        }
    }
    
    
    private static void tran3() throws Exception {
        final String sql = "INSERT INTO SomeTable(id, description) VALUES (333, :val)";

        Configuration.getSql2o().runInTransaction(new StatementRunnable() {
            public void run(Connection connection, Object argument) throws Throwable {
                Query query = connection.createQuery(sql);
                for (int i = 0; i < 5; i++){
                    query.addParameter("val", "foo" + i).addToBatch();
                }
                query.executeBatch();
            }
        });        
    }
    
    private static void tran2() throws Throwable  {
        Configuration.getSql2o().runInTransaction(new StatementRunnable() {
            public void run(Connection connection, Object argument) throws Throwable {
                String sql = "INSERT INTO SomeTable(id, description) VALUES (222, :val)";
                connection.createQuery(sql).addParameter("val", "val1").executeUpdate();
                connection.createQuery(sql).addParameter("val", "val2").executeUpdate();
            }
        });       
    }
    
    
    private static void tran1() throws Throwable  {
        String sql = "INSERT INTO SomeTable(id, description) VALUES (111, :val)";
        
        Connection connection = null;
        try {
            connection = Configuration.getSql2o().beginTransaction();
            connection.createQuery(sql).addParameter("val", "val1").executeUpdate();
            connection.createQuery(sql).addParameter("val", "val2").executeUpdate();
            connection.commit();
        } catch(Throwable t){
            if (connection != null){
                connection.rollback();
            }
            throw t;
        }        
    }
}

